SQLFluffを完全に理解する

#SQL

#Google Cloud (GCP)

hanzawa.yuya

2023.05.01

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

Google Cloudのデータエンジニアをしています、はんざわです。
今回はSQLのリンターであるSQLFluffを触りながら理解を深めたいと思います。

検証環境

macOS： 13.3.1
Python： 3.9.5

SQLFluffとは

SQLFluffとは、SQLのフォーマットを自動で問題点の指摘や修正をしてくれるオープンソースサービスです。

さっそくインストールして使ってみたいと思います。

インストール

SQLFluffをインストールするにはPython3が必要です。

$ pip3 install sqlfluff

正常にインストールできているか確認します。

$ sqlfluff version
2.0.7

インストールが完了しました。実際にクエリを用意し、使ってみたいと思います。

さっそく使ってみる

sqlfluffには大きくlintとfixの2つの機能があります。
まずはlintを使ってみます。

※ 今回はBigQueryを想定した設定にしています

lintとは

lintの公式ドキュメント
lintはSQLのクエリを読み込んで問題点を指摘してくれます。

例として、以下のようなクエリを用意しました。

$ cat example1.sql

       SELECT
  AAA, BBB, CCC
  fRom
TMP

このクエリをlintに読み込ませてみます。

$ sqlfluff lint example1.sql --dialect=bigquery

== [example1.sql] FAIL                                                                                 
L:   1 | P:   1 | LT01 | Expected only single space before 'SELECT' keyword.
                       | Found '       '. [layout.spacing]
L:   1 | P:   1 | LT02 | First line should not be indented.
                       | [layout.indent]
L:   1 | P:   1 | LT13 | Files must not begin with newlines or whitespace.
                       | [layout.start_of_file]
L:   1 | P:   8 | LT09 | Select targets should be on a new line unless there is
                       | only one select target.
                       | [layout.select_targets]
L:   2 | P:   1 | LT02 | Expected indent of 4 spaces.
                       | [layout.indent]
L:   3 | P:   3 | CP01 | Keywords must be consistently upper case.
                       | [capitalisation.keywords]
L:   4 | P:   1 | LT02 | Expected indent of 4 spaces.
                       | [layout.indent]
L:   4 | P:   4 | LT12 | Files must end with a single trailing newline.
                       | [layout.end_of_file]
All Finished ? ?!

ここで出力の左からLは何行目かを表し、Pは何文字目かを表します。
その隣のLT〇〇やCP〇〇がエラーコードでエラーメッセージも一緒に確認できます。

エラーコードは次の公式ドキュメントで確認できます。
簡単なクエリのサンプル付きなのでエラーのクエリと比較することができ、非常に便利です。

dialectとは

最初のコマンドの引数で--dialect=bigqueryと指定しました。dialectは必須引数でどの種類のSQLを使うか指定する必要があります。
dialectは以下の中から選べます。

$ sqlfluff dialects

==== sqlfluff - dialects ====
ansi:                 ansi dialect [inherits from 'nothing']
athena:                athena dialect [inherits from 'ansi']
bigquery:            bigquery dialect [inherits from 'ansi']
clickhouse:        clickhouse dialect [inherits from 'ansi']
databricks:    databricks dialect [inherits from 'sparksql']
db2:                      db2 dialect [inherits from 'ansi']
duckdb:            duckdb dialect [inherits from 'postgres']
exasol:                exasol dialect [inherits from 'ansi']
greenplum:      greenplum dialect [inherits from 'postgres']
hive:                    hive dialect [inherits from 'ansi']
materialize:  materialize dialect [inherits from 'postgres']                                                        
mysql:                  mysql dialect [inherits from 'ansi']
oracle:                oracle dialect [inherits from 'ansi']
postgres:            postgres dialect [inherits from 'ansi']
redshift:        redshift dialect [inherits from 'postgres']
snowflake:          snowflake dialect [inherits from 'ansi']
soql:                    soql dialect [inherits from 'ansi']
sparksql:            sparksql dialect [inherits from 'ansi']
sqlite:                sqlite dialect [inherits from 'ansi']
teradata:            teradata dialect [inherits from 'ansi']
tsql:                    tsql dialect [inherits from 'ansi']

ルールのカスタマイズ

SQLFluffは全ルールや各ルールのカスタマイズ、ルールの適用範囲など細かく独自のルールをカスタマイズすることができます。

ルールの設定変更は、以下のファイルに設定を書き込むことで変更することができます。
今回の検証では.sqlfluffを使います。

setup.cfg
tox.ini
pep8.ini
.sqlfluff
pyproject.toml

設定ファイルを以下のように設定し、作業ディレクトリ配下に設置しました。

[sqlfluff]

# 上記のdialectの中から選ぶ
dialect = bigquery

# ルールの中から取り除くものを選ぶ
exclude_rules = L036

この例の場合、L036のルールを除いた全てのルールが適用されます。
L036はSELECT句の対象が1つでない場合は改行しましょうというルールです。この例ではこのルールを取り除いているため、以下のようなクエリでも指摘されません。

$ cat example2.sql

SELECT
    AAA, BBB, CCC
FROM
    TMP;

$ sqlfluff lint example2.sql
All Finished ? ?!

このようにルールをlintに適用することができました。

この記事ではルールについて簡潔にしか説明していませんので（ルールの量が非常に多いため）公式ドキュメントをしっかり読むことを推奨します。

fixとは

fixの公式ドキュメント
lintはルールに沿って、問題点を指摘するだけでしたが、fixはクエリを修正してくれます。

$ cat example1.sql

       SELECT
  AAA, BBB, CCC
  fRom
TMP

$ sqlfluff fix example1.sql --dialect=bigquery

==== finding fixable violations ====
== [example1.sql] FAIL                                                                                 
L:   1 | P:   1 | LT01 | Expected only single space before 'SELECT' keyword.                           
                       | Found '       '. [layout.spacing]
L:   1 | P:   1 | LT02 | First line should not be indented.
                       | [layout.indent]
L:   1 | P:   8 | LT09 | Select targets should be on a new line unless there is
                       | only one select target.
                       | [layout.select_targets]
L:   2 | P:   1 | LT02 | Expected indent of 4 spaces.
                       | [layout.indent]
L:   3 | P:   3 | CP01 | Keywords must be consistently upper case.
                       | [capitalisation.keywords]
L:   4 | P:   1 | LT02 | Expected indent of 4 spaces.
                       | [layout.indent]
L:   4 | P:   4 | LT12 | Files must end with a single trailing newline.
                       | [layout.end_of_file]
==== fixing violations ====
7 fixable linting violations found

Are you sure you wish to attempt to fix these? [Y/n]

上記のコマンドを入力するとAre you sure you wish to attempt to fix these? [Y/n]と聞かれるのでYと入力すると以下のように続き、クエリを自動で修正してくれます。

Attempting fixes...
Persisting Changes...
== [example1.sql] FIXED
Done. Please check your files to confirm.
All Finished ? ?!

$ cat example1.sql

SELECT
    AAA,
    BBB,
    CCC
FROM
    TMP

fixもlint同様にルールを設定することができます。

おまけ： SQLFluffのVScode向け拡張機能を使ってみる

VScodeにSQLFluffの拡張機能を追加し、使ってみたいと思います。
利用するのは以下の拡張機能です。

VScodeの拡張機能検索画面でsqlfluffと検索し、インストールします。

細かなルールの設定はワークディレクトリ配下に.vscode/settings.jsonを設置し、追記することで変更できるようです。

公式ドキュメントに記載のようにsettings.jsonに直接ルールを記述することもできます。

  "sqlfluff.config": "${workspaceFolder}/.sqlfluff",
  "sqlfluff.dialect": "mysql",
  "sqlfluff.excludeRules": ["L009"],
  "sqlfluff.executablePath": "sqlfluff",
  "sqlfluff.ignoreLocalConfig": false,
  "sqlfluff.ignoreParsing": false,
  "sqlfluff.rules": [],
  "sqlfluff.suppressNotifications": false,
  "sqlfluff.workingDirectory": "",
  /* Linter */
  "sqlfluff.linter.arguments": [],
  "sqlfluff.linter.run": "onType",
  "sqlfluff.linter.diagnosticSeverity": "error",
  "sqlfluff.linter.diagnosticSeverityByRule": [
    {
      "rule": "L010",
      "severity": "warning"
    }
  ],
  "sqlfluff.linter.lintEntireProject": true,
  /* Formatter */
  "sqlfluff.format.arguments": ["--FIX-EVEN-UNPARSABLE"],
  "sqlfluff.format.enabled": true,

もしくは、.sqlfluffにルールを記載し、settings.jsonでパスだけ繋ぎルールを適用することもできます。
以下のような構造で.sqlfluffのルールをSQLに適用することができます。
チームで開発するようなケースの場合は、この方法の方がいいかもしれませんね。

/* ディレクトリ構造を確認 */
$ tree -a ./
./
├── .vscode
│   └── settings.json
├── linter
│   └── .sqlfluff
└── sql
    ├── example1.sql
    └── example2.sql

4 directories, 4 files

/* ファイルの中身を確認 */
$ cat .vscode/settings.json 
{
    "sqlfluff.config": "${workspaceFolder}/linter/.sqlfluff"
}

まとめ

今回はSQLFluffの使い方を紹介しました。
あくまで単体での使い方を紹介しましたがさらにGitHub Actionsなどと組み合わせたCI/CDの構築なども今後紹介したいと思います。
SQLのフォーマットをチーム独自で設定し、コードチェックと運用を行っていくことは非常に困難です。これらの処理を機械にやらせることでチームで統一させることができますので是非使ってみてください。